﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ThanhTu.GNC.Media.KQXS.DataAccess.DO;
using Oracle.ManagedDataAccess.Client;
using System.Data;

namespace ThanhTu.GNC.Media.KQXS.DataAccess.DAL
{
    public class ResultLotteryDAL : BaseDAL
    {
        public ResultLotteryDAL() : base() { }
        public int SaveLottery(ResultLotteryDO vResultLottery)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.SAVELOTTERYRESULT";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = vResultLottery.ProvinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_AWARDS_ID", OracleDbType.Varchar2);
                oraParam.Value = vResultLottery.KindOfawards;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_RESULT_TIME", OracleDbType.Date);
                oraParam.Value = vResultLottery.Opendate;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_RESULT_VALUE", OracleDbType.Varchar2);
                oraParam.Value = vResultLottery.Result;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
                { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public int DeleteLottery(string ProvinceID, DateTime INNGAY_MO)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.DELETELOTTERYRESULT";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = ProvinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_RESULT_TIME", OracleDbType.Date);
                oraParam.Value = INNGAY_MO;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
                { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public int StatusLotteryResult(string ProvinceID, DateTime INNGAY_MO)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.STATUSLOTTERYRESULT";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = ProvinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_RESULT_TIME", OracleDbType.Date);
                oraParam.Value = INNGAY_MO;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);
                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
                { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public List<ResultLotteryDO> SelectALL_KQ_QT()
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.GETALLRESULT_ADMIN";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);
                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;
                    List<ResultLotteryDO> vKindOfAwardsDOCollection = null;
                    DataRow dr = null;
                    ResultLotteryDO vResultLotteryDO = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                            vKindOfAwardsDOCollection = new List<ResultLotteryDO>();

                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                dr = dt.Rows[i];
                                vResultLotteryDO = new ResultLotteryDO();

                                if (!Convert.IsDBNull(dr["PROVINCEID"]))
                                { vResultLotteryDO.ProvinceID = Convert.ToString(dr["PROVINCEID"]); }

                                if (!Convert.IsDBNull(dr["AWARDSID"]))
                                { vResultLotteryDO.KindOfawards = Convert.ToInt32(dr["AWARDSID"]); }


                                if (!Convert.IsDBNull(dr["OPENDATE"]))
                                { vResultLotteryDO.Opendate = Convert.ToDateTime(dr["OPENDATE"]); }

                                if (!Convert.IsDBNull(dr["RESULTVALUE"]))
                                { vResultLotteryDO.Result = Convert.ToString(dr["RESULTVALUE"]); }

                                vKindOfAwardsDOCollection.Add(vResultLotteryDO);
                                dr = null;
                                vResultLotteryDO = null;
                            }

                            dt.Dispose();
                            dt = null;
                        }

                        ds.Dispose();
                    }
                    oraComm.Dispose();
                    oraParam = null;

                    return vKindOfAwardsDOCollection;
                }

            }
        }
        public List<ResultLotteryDO> Select_KQ_Tinh_QT(string ProvinceID, DateTime INNGAY_MO)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.GET_RESULT_ADMIN";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = ProvinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_RESULT_TIME", OracleDbType.Date);
                oraParam.Value = INNGAY_MO;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;
                    List<ResultLotteryDO> vKindOfAwardsDOCollection = null;
                    DataRow dr = null;
                    ResultLotteryDO vResultLotteryDO = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                            vKindOfAwardsDOCollection = new List<ResultLotteryDO>();

                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                dr = dt.Rows[i];
                                vResultLotteryDO = new ResultLotteryDO();

                                if (!Convert.IsDBNull(dr["PROVINCEID"]))
                                { vResultLotteryDO.ProvinceID = Convert.ToString(dr["PROVINCEID"]); }

                                if (!Convert.IsDBNull(dr["AWARDSID"]))
                                { vResultLotteryDO.KindOfawards = Convert.ToInt32(dr["AWARDSID"]); }


                                if (!Convert.IsDBNull(dr["OPENDATE"]))
                                { vResultLotteryDO.Opendate = Convert.ToDateTime(dr["OPENDATE"]); }

                                if (!Convert.IsDBNull(dr["RESULTVALUE"]))
                                { vResultLotteryDO.Result = Convert.ToString(dr["RESULTVALUE"]); }

                                vKindOfAwardsDOCollection.Add(vResultLotteryDO);
                                dr = null;
                                vResultLotteryDO = null;
                            }

                            dt.Dispose();
                            dt = null;
                        }

                        ds.Dispose();
                    }
                    oraComm.Dispose();
                    oraParam = null;

                    return vKindOfAwardsDOCollection;
                }
            }
        }
        public DataTable SelectAll_KQ_QT_Page(int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.GETALL_RESULT_ADMIN_PAGE";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable SelectAll_KQ_User(int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.GETALL_RESULT_USER";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable SelectAll_KQ_Province_User(string ProvinceID, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.GETALL_RESULT_PROVINCE_USER";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = ProvinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();

                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable SelectAll_KQ_Region_User(string RegionID, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.GETALL_RESULT_REGION_USER";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_REGION_ID", OracleDbType.Varchar2);
                oraParam.Value = RegionID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);
                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable SelectAll_KQ_Thongke(string thongke, string ProvinceID, DateTime To_date, DateTime From_date)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.STATISTIC_DAY";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_NUMBER", OracleDbType.Varchar2);
                oraParam.Value = thongke;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("PROVINCEID", OracleDbType.Varchar2);
                oraParam.Value = ProvinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_FROM_DATE", OracleDbType.Date);
                oraParam.Value = From_date;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_TO_DATE", OracleDbType.Date);
                oraParam.Value = To_date;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);


                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                        }
                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable SelectAll_KQ_Thongke_Thang(string thongke, string ProvinceID, string MONTH)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.STATISTIC_MONTH";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_NUMBER", OracleDbType.Varchar2);
                oraParam.Value = thongke;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("PROVINCEID", OracleDbType.Varchar2);
                oraParam.Value = ProvinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_MONTH", OracleDbType.Varchar2);
                oraParam.Value = MONTH;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable GETALL_RESULT_PROVINCE_DATE(DateTime date, string provinceid, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.GETALL_RESULT_PROVINCE_DATE";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_DATE", OracleDbType.Date);
                oraParam.Value = date;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = provinceid;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable GETALL_RESULT_REGION_DATE(DateTime date, string regionid, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.GETALL_RESULT_REGION_DATE";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_DATE", OracleDbType.Date);
                oraParam.Value = date;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_REGION_ID", OracleDbType.Varchar2);
                oraParam.Value = regionid;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);
                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable SelectAll_KQ_User_date(DateTime date, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "LOTTERYRESULT_PKG.GETALL_RESULT_USER_DATE";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_DATE", OracleDbType.Date);
                oraParam.Value = date;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable Statistic_0_99(string provinceID, DateTime to_date, DateTime from_date, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "GNC_LOTTERY2.Statistic_0_99";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
                oraParam.Value = provinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_to_date", OracleDbType.Date);
                oraParam.Value = to_date;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_from_date", OracleDbType.Date);
                oraParam.Value = from_date;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable Statistic_0_To_9(string provinceID, DateTime to_date, DateTime from_date, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "GNC_LOTTERY2.Statistic_0_To_9";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
                oraParam.Value = provinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_to_date", OracleDbType.Date);
                oraParam.Value = to_date;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_from_date", OracleDbType.Date);
                oraParam.Value = from_date;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable Statistic_Spec_Month(string provinceID, string date, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "GNC_LOTTERY2.Statistic_Spec_Month";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
                oraParam.Value = provinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_year", OracleDbType.Varchar2);
                oraParam.Value = date;
                oraComm.Parameters.Add(oraParam);


                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable Statistic_Spec_Month_2(string provinceID, string date, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "GNC_LOTTERY2.Statistic_Spec_Month_2";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
                oraParam.Value = provinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_year", OracleDbType.Varchar2);
                oraParam.Value = date;
                oraComm.Parameters.Add(oraParam);


                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable STATISTIC_SPEC_WEEK(string provinceID, string date, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "GNC_LOTTERY2.STATISTIC_SPEC_WEEK";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
                oraParam.Value = provinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_year", OracleDbType.Varchar2);
                oraParam.Value = date;
                oraComm.Parameters.Add(oraParam);


                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable STATISTIC_SPEC_WEEK_2(string provinceID, string date, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "GNC_LOTTERY2.STATISTIC_SPEC_WEEK_2";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
                oraParam.Value = provinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_year", OracleDbType.Varchar2);
                oraParam.Value = date;
                oraComm.Parameters.Add(oraParam);


                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable Statistic_2SPEC(string number, string provinceID, DateTime to_date, DateTime from_date)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "GNC_LOTTERY2.Statistic_2SPEC";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_Number", OracleDbType.Varchar2);
                oraParam.Value = number;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
                oraParam.Value = provinceID;
                oraComm.Parameters.Add(oraParam);


                oraParam = new OracleParameter("IN_to_date", OracleDbType.Date);
                oraParam.Value = to_date;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_from_date", OracleDbType.Date);
                oraParam.Value = from_date;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
    }
}
